/*
 * MySQL configuration, insert and search.
 */
package com.pressa.client.service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author Lucas Borri (lborri@pressa.com.ar)
 */
public class MySQLService {

    private String login;
    private String password;
    private String url;

    public void configDB(String db_connect, String db_user, String db_pass) {
        this.url = "jdbc:mysql://localhost/" + db_connect;
        this.login = db_user;
        this.password = db_pass;
    }

    public void tryConnectDB() {
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, login, password);
            if (conn != null) {
                System.out.println("Conexión a base de datos " + url + " ... Ok");
                conn.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            System.out.println("Hubo un problema al intentar conectarse con la "
                    + "base de datos " + url);
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        }
    }

    public String getPacket(String id) {
        String packet = null;
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, login, password);
            if (conn == null) {
                System.out.println("No se pudo conectar a la base de datos!");
                conn.close();
                throw new Exception("Sin conexion a la DB");
            }
            java.sql.Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("select * from report where id_packet = '" + id + "'");
            if (!rs.next()) {
                System.out.println("Sin reportes");
                throw new Exception("Sin reportes");
            }
            System.out.println(rs.getString("packet"));
            packet = rs.getString("packet");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return packet;
    }
    
    public boolean insertPacket(String packet) {
        Connection conn = null;
        boolean done = false;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, login, password);
            if (conn == null) {
                System.out.println("No se pudo conectar a la base de datos!");
                conn.close();
                throw new Exception("Error");
            }
            java.sql.Statement st = conn.createStatement();
            int status = st.executeUpdate("insert into report (id_packet, "
                    + "status_packet, packet) values ('"
                    + packet.substring(packet.indexOf("=") + 1, packet.indexOf("=") + 5)
                    + "' , '1', '" + packet + "')");
            if (status == 1) {
                done = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return done;
    }
     public ResultSet getReportsResultSet (String id) {
        Connection conn = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, login, password);
            if (conn == null) {
                System.out.println("No se pudo conectar a la base de datos!");
                conn.close();
                throw new Exception("Sin conexion a la DB");
            }
            java.sql.Statement st = conn.createStatement();
            rs = st.executeQuery("select * from report where id_packet = '" + id + "'");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return rs;
    }
     
    public ResultSet getSpecificResultSet (String table, String column, String value) {
        Connection conn = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, login, password);
            if (conn == null) {
                System.out.println("No se pudo conectar a la base de datos!");
                conn.close();
                throw new Exception("Sin conexion a la DB");
            }
            java.sql.Statement st = conn.createStatement();
            rs = st.executeQuery("select * from "+table+" where "+column+" = '"+value+"'");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return rs;
    }
}
